package com.roc.service.impl;
import java.io.File;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.roc.bean.ApplyChannel;
import com.roc.dao.DaoException;
import com.roc.dao.IBaseDao;
import com.roc.dao.ISQLBaseDao;
import com.roc.pojo.Operators;
import com.roc.pojo.TblSettleItem;
import com.roc.service.IApplyMoneyService;
import com.roc.service.ServiceException;
import com.roc.util.Constance;
import com.roc.util.MD5Utils;
import com.roc.util.constant.SysLogType;
import com.roc.web.action.settlemgr.SettlementApplyMoneySearchCondition;
@Service
@Transactional
public class ApplyMoneyServiceImpl implements IApplyMoneyService{
	private static Logger logger = LogManager.getLogger(ApplyMoneyServiceImpl.class);
	
	@Autowired
	ISQLBaseDao sqlBaseDao;
	@Autowired
	private IBaseDao baseDao;
	@Override
	@Transactional(rollbackFor=ServiceException.class)
	public synchronized String applySettlementMoney(SettlementApplyMoneySearchCondition condition,Operators op) throws ServiceException {
		String msg="";//返回状态信息
		String channel="",remark="";
		//1.首先拼接条件语句(此处由于都是从主库进行操作，所以并没有使用原有的DG公共方法)
		String stitchSql="";
		List<Object> conditionList=new ArrayList<Object>();//用于查询
		try {
				if(Constance.isNotEmpty(condition.getChannel())){//通道
					channel=condition.getChannel().trim();
				}else{
					msg="出款通道不能为空";
					return msg;
				}
				if(Constance.isNotEmpty(condition.getRemark())){//备注
					remark=condition.getRemark().trim();
				}else{
					msg="请填写出款备注";
					return msg;
				}
				if(Constance.isNotEmpty(condition.getMchtCd())){//商户号
					stitchSql=stitchSql+" and a.mcht_cd = ? ";
					conditionList.add(condition.getMchtCd().trim());
				}		
				if(Constance.isNotEmpty(condition.getMchtName())){//商户名
					stitchSql=stitchSql+" and a.mcht_name like ? ";
					conditionList.add("%"+condition.getMchtName().trim()+"%");
				}
				if(Constance.isNotEmpty(condition.getAccountNo())){//账号
					stitchSql=stitchSql+" and a.account = ? ";
					conditionList.add(condition.getAccountNo().trim());
				}		
				if(Constance.isNotEmpty(condition.getApplyDateMin())){//清算起始日期
					stitchSql=stitchSql+" and a.settledate >= ? ";
					conditionList.add(condition.getApplyDateMin().trim());
				}
				if(Constance.isNotEmpty(condition.getApplyDateMax())){//清算结束日期
					stitchSql=stitchSql+" and a.settledate <= ? ";
					conditionList.add(condition.getApplyDateMax().trim());
				}
				if(Constance.isNotEmpty(condition.getOrg())){//机构
					stitchSql=stitchSql+" and a.ins_id_cd = ? ";
					conditionList.add(condition.getOrg().trim());
				}
				if(Constance.isNotEmpty(condition.getNature())){//性质
					stitchSql=stitchSql+" and a.acc_type = ? ";
					conditionList.add(condition.getNature().trim());
				}
				if(Constance.isNotEmpty(condition.getType())){//类型
					stitchSql=stitchSql+" and a.remark = ? ";
					conditionList.add(condition.getType().trim());
				}
				if(Constance.isNotEmpty(condition.getAmtMax())){
					stitchSql=stitchSql+" and a.is_combing = ? ";
					conditionList.add(condition.getCombingName());
				}
				//构造条件,方便以下可以共用
				Object[] parmas=new Object[conditionList.size()];
				for(int i=0;i<conditionList.size();i++){
					parmas[i]=conditionList.get(i);
				}
				//2.首先查出是否存在符合条件并且状态为‘1’结算完成的所有数据List
				String sql="select a.id,a.ins_id_cd,a.mcht_cd,a.mcht_name,a.trans_amt,a.total_num,a.mcht_fee,a.chl_true_fee,a.mcht_set_amt,a.bank_name,"
						+"a.bank_code,a.account,a.account_name,a.acc_type,a.settledate,a.settleflag,a.setdate,a.freezeflag,a.dt_create,a.dt_update,a.remark,"
						+"a.remark1,a.remark2,a.remark3,a.remark4,a.freeze_amt,a.un_freeze_amt,a.is_combing,a.serial_no,a.count,a.change_flag,a.remark5 from t_settle_item a where a.settleflag='1' ";		
				sql=sql+stitchSql;//带条件的完整SQL
				List<Object> list=null;
					list=sqlBaseDao.queryAll(sql, parmas);
					if(list!=null && list.size()>0){
						double total=0;//出款金额
						int count=0;//出款笔数
						for(int i=0;i<list.size();i++){
							Object[] current=(Object[]) list.get(i);
							int effect=handleSplitATM(current, condition.getSplit(),op,channel,remark);
							if(effect>0){
								count=count+1;
								total=total+Double.parseDouble(Constance.Null2EmptyTrim(current[26]));//累加
							}
						}
						//处理金额累计
						DecimalFormat df=new DecimalFormat("0.00");
						msg="请求"+count+"项出款成功，共计出款金额"+df.format(total)+"元";						
					}else{
						msg="没有符合条件的出款条目";
					}
		} catch (DaoException e) {
			logger.debug(e.getMessage());
			throw new ServiceException("请求出款出错~");
		}
		return msg;
	}
	
	@Override
	@Transactional(rollbackFor=ServiceException.class)
	public synchronized String applySettlementMoneySimple(SettlementApplyMoneySearchCondition condition,String[] id, Operators op) throws ServiceException {
		String msg="";//返回信息
		String channel="",remark="";
		try {
			if(Constance.isNotEmpty(condition.getChannel())){//通道
				channel=condition.getChannel().trim();
			}else{
				msg="出款通道不能为空";
				return msg;
			}
			if(Constance.isNotEmpty(condition.getRemark())){//备注
				remark=condition.getRemark().trim();
			}else{
				msg="请填写出款备注";
				return msg;
			}
			String sql="select a.id,a.ins_id_cd,a.mcht_cd,a.mcht_name,a.trans_amt,a.total_num,a.mcht_fee,a.chl_true_fee,a.mcht_set_amt,a.bank_name,"
					+"a.bank_code,a.account,a.account_name,a.acc_type,a.settledate,a.settleflag,a.setdate,a.freezeflag,a.dt_create,a.dt_update,a.remark,"
					+"a.remark1,a.remark2,a.remark3,a.remark4,a.freeze_amt,a.un_freeze_amt,a.is_combing,a.serial_no,a.count,a.change_flag,a.remark5 from t_settle_item a where a.settleflag='1' and a.remark1 = ? ";		
			Object[] parmas=null;
			double total=0;//出款金额
			int count=0;//出款笔数
			for(int i=0;i<id.length;i++){
					parmas=new Object[1];
					parmas[0]=id[i];
					Object[] item=(Object[]) sqlBaseDao.findObj(sql, parmas);
					int effect=handleSplitATM(item, condition.getSplit(),op,channel,remark);
					if(effect>0){
						count=count+1;
						total=total+Double.parseDouble(Constance.Null2EmptyTrim(item[26]));//累加
					}
				}
				//处理金额累计
				DecimalFormat df=new DecimalFormat("0.00");
				msg="请求"+count+"项出款成功，共计出款金额"+df.format(total)+"元";				
			}catch (DaoException e) {
				logger.debug(e.getMessage());
				throw new ServiceException("单独请求出款出错~");
			}
			return msg;
	}
	
	@Override
	public List<ApplyChannel> selectChannelList() throws ServiceException {
		List<ApplyChannel> channelList=null;
		try {
			String sql="select code,name from t_apply_channel";
			List<Object> list = sqlBaseDao.queryAll(sql);
			if(list.size()>0){
				channelList = new ArrayList<ApplyChannel>();
				for(int i=0;i<list.size();i++){
					Object[] obj=(Object[]) list.get(i);
					ApplyChannel channel=new ApplyChannel();
					channel.setCode(Constance.Null2EmptyTrim(obj[0]));
					channel.setName(Constance.Null2EmptyTrim(obj[1]));
					channelList.add(channel);
				}
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage());
			throw new ServiceException("查询出款通道出错~");
		}
		return channelList;
	}

	@Override
	public String hangPaySettlement(String[] merchantsn, Operators op)
			throws ServiceException {
			String msg="操作失败";//返回信息
			try{
			String selSql=" select a.settleflag from t_settle_item a where a.remark1 = ? ";
			String updateSql="update t_settle_item set remark2=?,settleflag='0',dt_update=sysdate where remark1 = ? and settleflag='1' ";
			Object[] parmas=null;
			List<Object> param=null;
			int applyCount=0;//计算有多少是已经处理过的
				for(int i=0;i<merchantsn.length;i++){
					parmas=new Object[1];
					param=new ArrayList<Object>();
					param.add(op.getAccountNo());
					parmas[0]=merchantsn[i];
					param.add(merchantsn[i]);
					Object obj=sqlBaseDao.findObj(selSql, parmas);
					if(obj!=null){
						String status=Constance.Null2EmptyTrim(obj);
						if("1".equals(status)){//未处理过
							int rows=sqlBaseDao.deleteBySql(updateSql, param);//符合条件修改状态
							if(rows>0){
								baseDao.addSyslog(SysLogType.PAY_HANG_SETTLEMENT_MONEY.getCode(), SysLogType.PAY_HANG_SETTLEMENT_MONEY.getName(), op.getAccountNo(), "操作员"+op.getAccountNo()+"挂起出款流水号为"+merchantsn[i]);
							}
						}else{
							applyCount++;
						}
					}
				}
				if(applyCount>0){
					msg="此次有"+(merchantsn.length-applyCount)+"项出款挂起成功，其中所选的"+applyCount+"项已处理";
				}else{
					msg="挂起"+merchantsn.length+"项成功";
				}
		} catch (DaoException e) {
			logger.debug(e.getMessage());
			throw new ServiceException("出款挂起出错~");
		}
		return msg;
	}

	@Override
	public String freePaySettlement(String[] merchantsn, Operators op)
			throws ServiceException {
				String msg="操作失败";//返回信息
				try{
				String selSql=" select a.settleflag from t_settle_item a where a.remark1 = ? ";
				String updateSql="update t_settle_item set remark2=?,settleflag='1',dt_update=sysdate where remark1 = ? and settleflag='0' ";
				Object[] parmas=null;
				List<Object> param=null;
				int applyCount=0;//计算有多少是已经处理过的
					for(int i=0;i<merchantsn.length;i++){
						parmas=new Object[1];
						param=new ArrayList<Object>();
						param.add(op.getAccountNo());
						parmas[0]=merchantsn[i];
						param.add(merchantsn[i]);
						Object obj=sqlBaseDao.findObj(selSql, parmas);
						if(obj!=null){
							String status=Constance.Null2EmptyTrim(obj);
							if("0".equals(status)){//未处理过
								int rows=sqlBaseDao.deleteBySql(updateSql, param);//符合条件修改状态
								if(rows>0){
									baseDao.addSyslog(SysLogType.PAY_FREE_SETTLEMENT_MONEY.getCode(), SysLogType.PAY_FREE_SETTLEMENT_MONEY.getName(), op.getAccountNo(), "操作员"+op.getAccountNo()+"释放出款流水号为"+merchantsn[i]);
								}
							}else{
								applyCount++;
							}
						}
					}
					if(applyCount>0){
						msg="此次有"+(merchantsn.length-applyCount)+"项出款收回成功，其中所选的"+applyCount+"项已处理";
					}else{
						msg="收回"+merchantsn.length+"项成功";
					}
			} catch (DaoException e) {
				logger.debug(e.getMessage());
				throw new ServiceException("出款收回出错~");
			}
			return msg;
		}

	@Override
	public Map<String, Object> insertPaySettlement(File file, Operators op)
			throws ServiceException {
		String updateSql="update t_settle_item set remark2=?,settleflag='0',dt_update=sysdate where mcht_cd = ? and settledate=? and settleflag='1' ";		
		String selSql="select count(1) from t_settle_item s where s.mcht_cd = ? and settledate=? and settleflag='1' ";
		Map<Integer,List<Object>> uploadData=new HashMap<Integer, List<Object>>();//存放上传数据
		Map<String,Object> resultMap=new HashMap<String, Object>();//上传文件错误原因及定位
		int count=0;
		try{
			//上传Excel文件流转换为Map集合
			logger.info("上传Excel文件流转换为Map集合");
			boolean uploadResult=transfromToExcel(file,uploadData,resultMap);
			if(!uploadResult)
			{
				return resultMap;//返回错误原因
			}
			//验证Excel表数据正确性
			logger.info("验证Excel表数据正确性");
			boolean correntValidate=verifyDataCorrectness(uploadData, resultMap);
			if(!correntValidate)
			{
				return resultMap;//返回校验原因
			}
			//导入数据库
			logger.info("开始进行批量导入操作");
			Iterator<Entry<Integer, List<Object>>> it=uploadData.entrySet().iterator();
			//检测是否匹配数据
			while(it.hasNext())
			{
				Entry<Integer, List<Object>> entry=it.next();
				List<Object> list=entry.getValue();
				//循环列					
				Object[] parmas=new Object[2];
				parmas[0]=Constance.Null2EmptyTrim(list.get(0));
				parmas[1]=Constance.Null2EmptyTrim(list.get(1));
				Object obj=sqlBaseDao.findObj(selSql, parmas);
				if(Integer.valueOf(String.valueOf(obj))==0)
				{
					String errorMsg="导入失败,没有匹配到第"+(entry.getKey()+1)+"行第1列的商户号";
					resultMap.put("result",errorMsg);
					logger.info("停止批量导入,"+errorMsg);
					return resultMap;
				}
			}
			//开始导入数据
			it=uploadData.entrySet().iterator();
			while(it.hasNext())
			{
				List<Object> param=new ArrayList<Object>();//参数
				param.add(op.getAccountNo());
				Entry<Integer, List<Object>> entry=it.next();
				List<Object> list=entry.getValue();
				//循环列
				param.add(Constance.Null2EmptyTrim(list.get(0)));
				param.add(Constance.Null2EmptyTrim(list.get(1)));
				int rows=sqlBaseDao.deleteBySql(updateSql, param);
				if(rows>0){
					count++;
					baseDao.addSyslog(SysLogType.PAY_HANG_SETTLEMENT_MONEY.getCode(), SysLogType.PAY_HANG_SETTLEMENT_MONEY.getName(), op.getAccountNo(), "操作员"+op.getAccountNo()+"导入挂起商户号为"+Constance.Null2EmptyTrim(list.get(0)));
				}else{
					String error_reason="成功导入前"+count+"条数据,第"+(entry.getKey()+1)+"行第1列的商户号已被处理";
					logger.info(error_reason);
					resultMap.put("result", error_reason);
					return resultMap;
				}
			}
			logger.info("批量导入操作完成");
		}catch(Exception e){
			logger.debug(e.getMessage(), e);
			throw new ServiceException("批量导入出款挂起商户失败");
		}
		resultMap.put("result","导入成功!总计导入"+count+"条数据");
		return resultMap;
	}

	/**
	 * 将File装换成Map集合
	 * @param file
	 * @param map
	 */
	@SuppressWarnings("deprecation")
	protected boolean transfromToExcel(File file,Map<Integer,List<Object>> map,Map<String,Object> resultMap)
	{
		try {
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			XSSFWorkbook xwb = new XSSFWorkbook(file.getPath());
			// 得到Excel工作表对象
			XSSFSheet sheet = xwb.getSheetAt(0);
			// 得到行数
			int trLength = sheet.getPhysicalNumberOfRows();
			if(trLength == 0){
				logger.info("上传文件缺少列名");
				resultMap.put("result", "上传文件缺少列名");
				return false;
			}			
			logger.info("Excel中记录的总行数是：" + (trLength - 1));			
			// 每条记录列数
			Row row = sheet.getRow(0);
						
			int tdLength = row.getPhysicalNumberOfCells();
			if(tdLength<2){
				logger.info("上传的文件缺少必需的列名");
				resultMap.put("result", "上传的文件缺少必需的列名");
				return false;
			}
			if(tdLength>2){
				logger.info("上传的文件存在多余的列名");
				resultMap.put("result", "上传的文件存在多余的列名");
				return false;
			}
			logger.info("每条记录的列数："+tdLength);
				
			//验证标题是否一致
			String reason_error=null;
			String[] title={"商户号","结算日期"};
			String titleName=null;
			for(int i=0;i<title.length;i++)
			{
				row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
				titleName=row.getCell(i).getStringCellValue();
				if(!titleName.equals(title[i]))
				{
					reason_error="第1行第"+(i+1)+"列的列名不正确,正确的列名为【"+title[i]+"】";
					logger.info(reason_error);
					resultMap.put("result", reason_error);
					return false;
				}
			}
			if(trLength == 1){
				logger.info("上传文件内容为空");
				resultMap.put("result", "上传文件内容为空");
				return false;
			}
			for (int i = 1; i < trLength; i++) {
				List<Object> tdList = new ArrayList<Object>();
				Row r = sheet.getRow(i);
				
				for (int j = 0; j < tdLength; j++) {
					Cell c = r.getCell(j);
					/*
					 * 将所有列的内容都设置成String类型，否则会出Excel异常： Cannot get a text value
					 * from a numeric cell
					 */
					if (c != null) {
						c.setCellType(Cell.CELL_TYPE_STRING);
						tdList.add(c.getStringCellValue());
					}else{
						tdList.add("");
					}					
				}
				map.put(i, tdList);
			}
		} catch (Exception e) {
			logger.error("将Excel中数据封装到List容器中异常：" + e.getMessage());
			resultMap.put("result", "将Excel中数据封装到List容器中异常");
			return false;
		}
		return true;
	}
	
	/**
	 * 验证数据正确性
	 * @param map
	 */
	public boolean verifyDataCorrectness(Map<Integer,List<Object>> map,Map<String,Object> resultMap)
	{
		Iterator<Entry<Integer, List<Object>>> it=map.entrySet().iterator();
		String error_reason=null;
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();			
			//商户号
			String regex="^\\d{15}$";
			String num=Constance.Null2EmptyTrim(list.get(0));
			if(!Pattern.matches(regex,num))
			{
				error_reason="第"+(entry.getKey()+1)+"行第1列商户号只能包含数字字符且长度为15，也不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
			String settleRex="^\\d{8}$";//结算日期
			String settleDate=Constance.Null2EmptyTrim(list.get(1));
			if(!Pattern.matches(settleRex,settleDate))
			{
				error_reason="第"+(entry.getKey()+1)+"行第2列结算日期只能包含数字字符且长度为8，也不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
		}
		return true;
	}

	@Override
	public Map<String, Object> insertPayFreeSettlement(File file, Operators op)
			throws ServiceException {
		String updateSql="update t_settle_item set remark2=?,settleflag='1',dt_update=sysdate where mcht_cd = ? and settledate=? and settleflag='0' ";		
		String selSql="select count(1) from t_settle_item s where s.mcht_cd = ? and settledate=? and settleflag='0' ";
		Map<Integer,List<Object>> uploadData=new HashMap<Integer, List<Object>>();//存放上传数据
		Map<String,Object> resultMap=new HashMap<String, Object>();//上传文件错误原因及定位
		int count=0;
		try{
			//上传Excel文件流转换为Map集合
			logger.info("上传Excel文件流转换为Map集合");
			boolean uploadResult=transfromToExcel(file,uploadData,resultMap);
			if(!uploadResult)
			{
				return resultMap;//返回错误原因
			}
			//验证Excel表数据正确性
			logger.info("验证Excel表数据正确性");
			boolean correntValidate=verifyDataCorrectness(uploadData, resultMap);
			if(!correntValidate)
			{
				return resultMap;//返回校验原因
			}
			//导入数据库
			logger.info("开始进行批量导入操作");
			Iterator<Entry<Integer, List<Object>>> it=uploadData.entrySet().iterator();
			//检测是否匹配数据
			while(it.hasNext())
			{
				Entry<Integer, List<Object>> entry=it.next();
				List<Object> list=entry.getValue();
				//循环列					
				Object[] parmas=new Object[2];
				parmas[0]=Constance.Null2EmptyTrim(list.get(0));
				parmas[1]=Constance.Null2EmptyTrim(list.get(1));
				Object obj=sqlBaseDao.findObj(selSql, parmas);
				if(Integer.valueOf(String.valueOf(obj))==0)
				{
					String errorMsg="导入失败,没有匹配到第"+(entry.getKey()+1)+"行第1列的商户号";
					resultMap.put("result",errorMsg);
					logger.info("停止批量导入,"+errorMsg);
					return resultMap;
				}
			}
			//开始导入数据
			it=uploadData.entrySet().iterator();
			while(it.hasNext())
			{
				List<Object> param=new ArrayList<Object>();//参数
				param.add(op.getAccountNo());
				Entry<Integer, List<Object>> entry=it.next();
				List<Object> list=entry.getValue();
				//循环列
				param.add(Constance.Null2EmptyTrim(list.get(0)));
				param.add(Constance.Null2EmptyTrim(list.get(1)));
				int rows=sqlBaseDao.deleteBySql(updateSql, param);
				if(rows>0){
					count++;
					baseDao.addSyslog(SysLogType.PAY_FREE_SETTLEMENT_MONEY.getCode(), SysLogType.PAY_FREE_SETTLEMENT_MONEY.getName(), op.getAccountNo(), "操作员"+op.getAccountNo()+"导入挂起回收商户号为"+Constance.Null2EmptyTrim(list.get(0)));
				}else{
					String error_reason="成功导入前"+count+"条数据,第"+(entry.getKey()+1)+"行第1列的商户号已被处理";
					logger.info(error_reason);
					resultMap.put("result", error_reason);
					return resultMap;
				}
			}
			logger.info("批量导入操作完成");
		}catch(Exception e){
			logger.debug(e.getMessage(), e);
			throw new ServiceException("批量导入挂起收回商户失败");
		}
		resultMap.put("result","导入成功!总计导入"+count+"条数据");
		return resultMap;
	}

	@Override
	public synchronized Map<String, Object> handleCombineAmt(
			SettlementApplyMoneySearchCondition searchCondition,Operators op)
			throws ServiceException {
		Map<String, Object> map=new HashMap<String, Object>();
		String stitchSql="select a.remark1,a.un_freeze_amt from t_settle_item a where a.settleflag='1' ";
		List<Object> conditionList=new ArrayList<Object>();//用于查询
		stitchSql=stitchSql+" and a.is_combing is null ";
		if(Constance.isNotEmpty(searchCondition.getMchtCd())){//商户号
			stitchSql=stitchSql+" and a.mcht_cd = ? ";
			conditionList.add(searchCondition.getMchtCd().trim());
		}		
		if(Constance.isNotEmpty(searchCondition.getMchtName())){//商户名
			stitchSql=stitchSql+" and a.mcht_name like ? ";
			conditionList.add("%"+searchCondition.getMchtName().trim()+"%");
		}
		if(Constance.isNotEmpty(searchCondition.getAccountNo())){//账号
			stitchSql=stitchSql+" and a.account = ? ";
			conditionList.add(searchCondition.getAccountNo().trim());
		}		
		if(Constance.isNotEmpty(searchCondition.getApplyDateMin())){//清算起始日期
			stitchSql=stitchSql+" and a.settledate >= ? ";
			conditionList.add(searchCondition.getApplyDateMin().trim());
		}
		if(Constance.isNotEmpty(searchCondition.getApplyDateMax())){//清算结束日期
			stitchSql=stitchSql+" and a.settledate <= ? ";
			conditionList.add(searchCondition.getApplyDateMax().trim());
		}
		if(Constance.isNotEmpty(searchCondition.getOrg())){//机构
			stitchSql=stitchSql+" and a.ins_id_cd = ? ";
			conditionList.add(searchCondition.getOrg().trim());
		}
		if(Constance.isNotEmpty(searchCondition.getNature())){//性质
			stitchSql=stitchSql+" and a.acc_type = ? ";
			conditionList.add(searchCondition.getNature().trim());
		}
		if(Constance.isNotEmpty(searchCondition.getType())){//类型
			stitchSql=stitchSql+" and a.remark = ? ";
			conditionList.add(searchCondition.getType().trim());
		}
		
		Object[] parmas=new Object[conditionList.size()];
		for(int i=0;i<conditionList.size();i++){
			parmas[i]=conditionList.get(i);
		}
		//先出所有符合条件的，然后进行金额拼凑，塞进amtList里进行标记
		List<Object> list=null;
		List<String> amtList=new ArrayList<String>();//需要修改标志的部分
		try {
			list=sqlBaseDao.queryAll(stitchSql, parmas);
			if(list.size()>0){
				logger.info("开始拼凑金额");
				double temp=0,store=0;//临时值
				double max=Double.valueOf(searchCondition.getAmtMax())*10000;//最大值
				for(int i=0;i<list.size();i++){
					Object[] obj=(Object[]) list.get(i);//取出来
					double param=Double.valueOf(String.valueOf(obj[1]));//参数值
					temp=temp+param;
					if(max-temp>=0){
						store=store+param;
						amtList.add(String.valueOf(obj[0]));
					}else{
						break;
					}
				}
				logger.info("拼凑金额完成");
				//修改标志				
				logger.info("拼凑金额进行标记");
				String updateSQL=" update t_settle_item set is_combing=? where remark1=? and settleflag='1' ";	
				for(String serialNO:amtList){
					List<Object> targetList=new ArrayList<Object>();
					targetList.add(op.getAccountNo());
					targetList.add(serialNO);
					sqlBaseDao.deleteBySql(updateSQL, targetList);				
				}
				logger.info("拼凑金额标记完成");				
				DecimalFormat df=new DecimalFormat("0.00");
				String msg="拼凑完成，已拼凑出"+amtList.size()+"条记录，累计金额:"+df.format(store)+"元";
				map.put("result",msg);
			}else{
				map.put("error","匹配符合条件的记录为空");
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(), e);
			throw new ServiceException("拼凑金额标记失败!");
		}
		return map;
	}

	@Override
	public void clearOriginCombie(SettlementApplyMoneySearchCondition searchCondition,Operators op) throws ServiceException {
		
		String updateSQL=" update t_settle_item set is_combing=null where is_combing=? and settleflag='1' ";	
		List<Object> targetList=new ArrayList<Object>();
		targetList.add(op.getAccountNo());
		if(Constance.isNotEmpty(searchCondition.getApplyDateMin())){//清算起始日期
			updateSQL=updateSQL+" and settledate >= ? ";
			targetList.add(searchCondition.getApplyDateMin().trim());
		}
		if(Constance.isNotEmpty(searchCondition.getApplyDateMax())){//清算结束日期
			updateSQL=updateSQL+" and settledate <= ? ";
			targetList.add(searchCondition.getApplyDateMax().trim());
		}
		try {
				sqlBaseDao.deleteBySql(updateSQL,targetList);
				logger.info("清空原有标记成功");
		} catch (DaoException e) {
			logger.debug(e.getMessage(), e);
			throw new ServiceException("清空原有标记失败!");
		}
	}
	
	/**
	 * 拆分金额公共方法
	 * @param item
	 * @param flag
	 * @param op
	 * @param channel
	 * @param remark
	 * @param total
	 * @param count
	 * @throws ServiceException 
	 */
	protected int handleSplitATM(Object[] item,String flag,Operators op,String channel,String remark) throws ServiceException{
		String insertSQL="insert into t_settle_item(id,ins_id_cd,mcht_cd,mcht_name,trans_amt,"
				+ "total_num,mcht_fee,chl_true_fee,mcht_set_amt,bank_name,bank_code,account,"
				+ "account_name,acc_type,settledate,settleflag,setdate,freezeflag,dt_create,"
				+ "dt_update,remark,remark1,remark2,remark3,remark4,freeze_amt,un_freeze_amt,"
				+ "is_combing,serial_no,count,change_flag,remark5) "
				+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,systimestamp,null,?,?,?,?,?,?,?,?,?,?,?,?)";
		int status=0;	
		try {
				String updateOriginSQL=" update t_settle_item set remark2=?,remark3=?,remark4=?,settleflag='2',dt_update=sysdate,is_combing=null,serial_no=?,un_freeze_amt=? where remark1 = ? and settleflag='1' ";
				String updateSQL=" update t_settle_item set remark2=?,remark3=?,remark4=?,settleflag='2',dt_update=sysdate,is_combing=null,serial_no=? where remark1 = ? and settleflag='1' ";
				String originSerial=Constance.Null2EmptyTrim(item[21]);//原序列号
				DecimalFormat df=new DecimalFormat("0.00");
				BigDecimal oringinAMT=new BigDecimal(Constance.Null2EmptyTrim(item[26]));//原有金额
				BigDecimal mulp=new BigDecimal("50000");//倍数
				BigDecimal[] num=oringinAMT.divideAndRemainder(mulp);//结果数组,前者倍数后者余数	
				int temp=num[0].intValue();//取整
				if(temp>0 && "ok".equals(flag)){
					//先将多余的部分在原记录上修改,避免通过其他途径重复
					double lastAmt=num[1].doubleValue();
					if(lastAmt==0){//刚好整除
						lastAmt=50000;
						temp=temp-1;
					}
					List<Object> lastParam=new ArrayList<Object>();
					lastParam.add(op.getAccountNo());
					lastParam.add(channel);
					lastParam.add(remark);
					lastParam.add(originSerial);//生成的新序列号
					lastParam.add(df.format(lastAmt));
					lastParam.add(originSerial);
					int rows=sqlBaseDao.deleteBySql(updateOriginSQL,lastParam);
					if(rows>0){//修改成功才能进行拆分
						for(int i=0;i<temp;i++){
							//新流水号=机构号+结算日期+商户id+8位随机数+索引号
							String serial=Constance.Null2EmptyTrim(item[1])+MD5Utils.MD5Encode(Constance.Null2EmptyTrim(item[14])+Constance.Null2EmptyTrim(item[0])+Constance.randomNums(8)+String.valueOf(i+1)).toUpperCase();
							List<Object> insertNew=new ArrayList<Object>();
							insertNew.add(item[0]);
							insertNew.add(item[1]);
							insertNew.add(item[2]);
							insertNew.add(item[3]);
							if(item[4]==null){
								insertNew.add("");
							}else{
								insertNew.add(item[4]);
							}
							if(item[5]==null){
								insertNew.add("");
							}else{
								insertNew.add(item[5]);
							}
							if(item[6]==null){
								insertNew.add("");
							}else{
								insertNew.add(item[6]);
							}
							if(item[7]==null){
								insertNew.add("");
							}else{
								insertNew.add(item[7]);
							}
							if(item[8]==null){
								insertNew.add("");
							}else{
								insertNew.add(item[8]);
							}
							insertNew.add(item[9]);
							insertNew.add(item[10]);
							insertNew.add(item[11]);
							insertNew.add(item[12]);
							insertNew.add(item[13]);
							insertNew.add(item[14]);
							insertNew.add("2");//出款标志
							insertNew.add(item[16]);
							insertNew.add(item[17]);
						//	insertNew.add(item[18]);//时间自动创建
						//	insertNew.add(item[19]);//时间自动创建
							insertNew.add(item[20]);
							insertNew.add(serial);//主键流水号
							insertNew.add(op.getAccountNo());////操作账号
							insertNew.add(channel);//通道
							insertNew.add(remark);//备注
							if(item[25]==null){
								insertNew.add("");
							}else{
								insertNew.add(item[25]);
							}
							insertNew.add(df.format(50000));//拆分后每一笔出款金额
							insertNew.add("");//清除拼凑标志
							insertNew.add(serial);//新流水
							insertNew.add(item[29]);
							insertNew.add(item[30]);
							insertNew.add(item[31]);												
							//插入新的条目;
							sqlBaseDao.deleteBySql(insertSQL,insertNew);
						}					
						baseDao.addSyslog(SysLogType.APPLY_SETTLEMENT_MONEY.getCode(),SysLogType.APPLY_SETTLEMENT_MONEY.getName(), op.getAccountNo(),"操作员【"+op.getAccountNo()+"】拆分并申请了原流水为【"+originSerial+"】的出款项");
						status=1;
					}
				}else{
					List<Object> param=new ArrayList<Object>();
					param.add(op.getAccountNo());
					param.add(channel);
					param.add(remark);
					param.add(originSerial);
					param.add(originSerial);
					int rows=sqlBaseDao.deleteBySql(updateSQL,param);//符合条件修改状态
					if(rows>0){
						baseDao.addSyslog(SysLogType.APPLY_SETTLEMENT_MONEY.getCode(),SysLogType.APPLY_SETTLEMENT_MONEY.getName(), op.getAccountNo(),"操作员【"+op.getAccountNo()+"】申请了原流水为【"+originSerial+"】的出款项");
						status=1;
					}
				}
			} catch (DaoException e) {
				logger.debug(e.getMessage(),e);
				throw new ServiceException("拆分失败!");
			}
		return status;
	}
	
	/**
	 * 选择导出
	 * @param condition
	 * @param id
	 * @param op
	 * @return
	 * @throws ServiceException
	 */
	@Transactional(rollbackFor=ServiceException.class)
	public synchronized String selectExportUpdate(SettlementApplyMoneySearchCondition condition,String[] id, Operators op) throws ServiceException {
		String msg="";//返回信息
		try {
			String sql="select a.id,a.ins_id_cd,a.mcht_cd,a.mcht_name,a.trans_amt,a.total_num,a.mcht_fee,a.chl_true_fee,a.mcht_set_amt,a.bank_name,"
					+"a.bank_code,a.account,a.account_name,a.acc_type,a.settledate,a.settleflag,a.setdate,a.freezeflag,a.dt_create,a.dt_update,a.remark,"
					+"a.remark1,a.remark2,a.remark3,a.remark4,a.freeze_amt,a.un_freeze_amt,a.is_combing,a.serial_no,a.count,a.change_flag,a.remark5 from t_settle_item a where a.settleflag='1' and a.remark1 = ? ";		
			Object[] parmas=null;
			double total=0;//累计金额
			int count=0;//累计笔数
			for(int i=0;i<id.length;i++){
					parmas=new Object[1];
					parmas[0]=id[i];
					Object[] item=(Object[]) sqlBaseDao.findObj(sql, parmas);					
					int effect=UpdateTempChannelData(item, op);
					if(effect>0){
					count=count+1;
					total=total+Double.parseDouble(Constance.Null2EmptyTrim(item[26]));//累加
					}
				}
				//处理金额累计
				DecimalFormat df=new DecimalFormat("0.00");
				msg=count+"项汇出成功，共计汇出金额"+df.format(total)+"元";				
			}catch (DaoException e) {
				logger.debug(e.getMessage());
				throw new ServiceException("选择修改汇出出错~");
			}
			return msg;
	}
	/**
	 * 批量导出
	 * @param condition
	 * @param op
	 * @return
	 * @throws ServiceException
	 */
	@Transactional(rollbackFor=ServiceException.class)
	public synchronized String batchExportUpdate(SettlementApplyMoneySearchCondition condition,Operators op) throws ServiceException {
		String msg="";//返回状态信息
		//1.首先拼接条件语句(此处由于都是从主库进行操作，所以并没有使用原有的DG公共方法)
		String stitchSql="";
		List<Object> conditionList=new ArrayList<Object>();//用于查询
		try {
				
				if(Constance.isNotEmpty(condition.getMchtCd())){//商户号
					stitchSql=stitchSql+" and a.mcht_cd = ? ";
					conditionList.add(condition.getMchtCd().trim());
				}		
				if(Constance.isNotEmpty(condition.getMchtName())){//商户名
					stitchSql=stitchSql+" and a.mcht_name like ? ";
					conditionList.add("%"+condition.getMchtName().trim()+"%");
				}
				if(Constance.isNotEmpty(condition.getAccountNo())){//账号
					stitchSql=stitchSql+" and a.account = ? ";
					conditionList.add(condition.getAccountNo().trim());
				}		
				if(Constance.isNotEmpty(condition.getApplyDateMin())){//清算起始日期
					stitchSql=stitchSql+" and a.settledate >= ? ";
					conditionList.add(condition.getApplyDateMin().trim());
				}
				if(Constance.isNotEmpty(condition.getApplyDateMax())){//清算结束日期
					stitchSql=stitchSql+" and a.settledate <= ? ";
					conditionList.add(condition.getApplyDateMax().trim());
				}
				if(Constance.isNotEmpty(condition.getOrg())){//机构
					stitchSql=stitchSql+" and a.ins_id_cd = ? ";
					conditionList.add(condition.getOrg().trim());
				}
				if(Constance.isNotEmpty(condition.getNature())){//性质
					stitchSql=stitchSql+" and a.acc_type = ? ";
					conditionList.add(condition.getNature().trim());
				}
				if(Constance.isNotEmpty(condition.getType())){//类型
					stitchSql=stitchSql+" and a.remark = ? ";
					conditionList.add(condition.getType().trim());
				}
				if(Constance.isNotEmpty(condition.getAmtMax())){
					stitchSql=stitchSql+" and a.is_combing = ? ";
					conditionList.add(condition.getCombingName());
				}
				//构造条件,方便以下可以共用
				Object[] parmas=new Object[conditionList.size()];
				for(int i=0;i<conditionList.size();i++){
					parmas[i]=conditionList.get(i);
				}
				//2.首先查出是否存在符合条件并且状态为‘1’结算完成的所有数据List
				String sql="select a.id,a.ins_id_cd,a.mcht_cd,a.mcht_name,a.trans_amt,a.total_num,a.mcht_fee,a.chl_true_fee,a.mcht_set_amt,a.bank_name,"
						+"a.bank_code,a.account,a.account_name,a.acc_type,a.settledate,a.settleflag,a.setdate,a.freezeflag,a.dt_create,a.dt_update,a.remark,"
						+"a.remark1,a.remark2,a.remark3,a.remark4,a.freeze_amt,a.un_freeze_amt,a.is_combing,a.serial_no,a.count,a.change_flag,a.remark5 from t_settle_item a where a.settleflag='1' ";		
				sql=sql+stitchSql;//带条件的完整SQL
				List<Object> list=null;
					list=sqlBaseDao.queryAll(sql, parmas);
					if(list!=null && list.size()>0){
						double total=0;//累计金额
						int count=0;//累计笔数
						for(int i=0;i<list.size();i++){
							Object[] current=(Object[]) list.get(i);
							int effect=UpdateTempChannelData(current, op);
							if(effect>0){
								count=count+1;
								total=total+Double.parseDouble(Constance.Null2EmptyTrim(current[26]));//累加
							}
						}
						//处理金额累计
						DecimalFormat df=new DecimalFormat("0.00");
						msg=count+"项汇出成功，共计汇出金额"+df.format(total)+"元";						
					}else{
						msg="没有符合条件的条目";
					}
		} catch (DaoException e) {
			logger.debug(e.getMessage());
			throw new ServiceException("批量修改汇出出错~");
		}
		return msg;
	}
	/**
	 * 暂时更改上海银联对公明细报表
	 * @param item
	 * @param flag
	 * @param op
	 * @param channel
	 * @param remark
	 * @return
	 * @throws ServiceException
	 */
	protected int UpdateTempChannelData(Object[] item,Operators op) throws ServiceException{		
		String updateSQL=" update t_settle_item set remark2=?,remark3=?,remark4=?,settleflag='7',dt_update=sysdate,is_combing=null,serial_no=? where remark1 = ? and settleflag='1' ";
		String originSerial=Constance.Null2EmptyTrim(item[21]);//原序列号
		try {
			List<Object> param=new ArrayList<Object>();
			param.add(op.getAccountNo());
			param.add("SHDF");
			param.add("手工汇出");			
			param.add(originSerial);
			param.add(originSerial);		
			int rows=sqlBaseDao.deleteBySql(updateSQL,param);
			if(rows>0){
				return 1;
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("更改流水号"+originSerial+"状态失败~");
		}
		return 0;
	}
	
	public List<TblSettleItem> selectBySerialNo(String[] id)throws ServiceException{
		List<TblSettleItem> list=new ArrayList<TblSettleItem>();
		String hql="from TblSettleItem where remark1=?";
		Object[] parmas=null;
		try {
			for(int i=0;i<id.length;i++){
					parmas=new Object[1];
					parmas[0]=id[i];
					TblSettleItem item=(TblSettleItem) baseDao.createQueryUniqueResult(hql, parmas);
					list.add(item);
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("查询选取的汇出项详细信息失败~");
		}
		return list;		
	}

	@Override
	public Object[] findMainBank(String branchBankName, String branchBankCode)
			throws ServiceException {
		Object[] mainBank=null;
		String sql=" select g.bank_name,g.bank_code from tbl_bank_sh g where g.bank_name like ?  and g.bank_code like ?";
		Object[] parmas=null;
		try {
			parmas=new Object[2];
			parmas[0]=branchBankName.substring(0, 4)+"%";//取前4位
			parmas[1]=branchBankCode.substring(0, 3)+"%";//取前3位
			Object[] object=(Object[]) sqlBaseDao.findObj(sql, parmas);
			if(object!=null){
				mainBank=new Object[2];
				mainBank[0]=Constance.Null2EmptyTrim(object[0]);
				mainBank[1]=Constance.Null2EmptyTrim(object[1]);
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("查询分行失败~");
		}
		return mainBank;
	}
}
